package ch.jhc.listener;

import java.sql.SQLException;
import java.util.Date;

import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.servlet.annotation.WebListener;
import org.apache.commons.dbutils.QueryRunner;
import org.h2.jdbcx.JdbcDataSource;

import ch.jhc.Global;
import ch.jhc.bean.Article;
import ch.jhc.bean.User;
import ch.jhc.db.ArticleDao;
import ch.jhc.db.UserDao;

//当Servlet 容器启动或终止Web 应用时，会触发ServletContextEvent 事件，该事件由 ServletContextListener 来处理
@WebListener
public class MyContextListener implements ServletContextListener {

	//当Servlet 容器终止Web 应用时调用该方法
	//在调用该方法之前，容器会先销毁所有的Servlet 和Filter 过滤器。
	@Override
	public void contextDestroyed(ServletContextEvent sce) {
		
	}

	//当Servlet 容器启动Web 应用时调用该方法
	@Override
	public void contextInitialized(ServletContextEvent sce) {
		String path = sce.getServletContext().getRealPath("/h2demo");
		System.out.println(path);
		JdbcDataSource ds = new JdbcDataSource();
		ds.setURL("jdbc:h2:" + path);
		String sql = "create table if not exists user("
				+ "id int auto_increment primary key,"
				+ "username varchar(128) unique not null,"
				+ "password varchar(128) not null,"
				+ "filename varchar(128) not null,"
				+ "sex char(1) not null,"
				+ "interests varchar(128) not null)";
		String sql1 = "create table if not exists article("
				+ "id int auto_increment primary key,"
				+ "title varchar(256) not null,"
				+ "content text,"
				+ "publishDate datetime not null,"
				+ "userId int references user(id))";//对数据的完整性的保护
		
		//回复帖子的表
		String sql2 = "create table if not exists Reply("
				+ "id int auto_increment primary key,"
				+ "content text,"
				+ "publishDate datetime not null,"
				+ "userId int references user(id))";
		QueryRunner runner = new QueryRunner(ds);
		//并将要共享的QueryRunner提交到服务器的内存中去
		sce.getServletContext().setAttribute(Global.KEY_QUERY_RUNNER, runner);
		try {
			runner.execute(sql);
			runner.execute(sql1);
			runner.execute(sql2);
			insertData(runner);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private void insertData(QueryRunner runner) throws SQLException {
		for (int i = 0; i < 20; i++) {
			User user = new User("username" + i,"password"+ i ,"filename" + i,"M","interests" + i);
			if (UserDao.exists(runner, "username" + i)) {
				break;
			}
			UserDao.register(runner, user);
			User u = UserDao.login(runner, "username" + i, "password" + i);
			for(int j=0;j<100;j++) {
				Article article = new Article();
				article.setTitle("title" + j);
				article.setContent("content" + j);
				article.setPublishDate(new Date());
				article.setUserId(u.getId());
				ArticleDao.add(runner, article);
			}
		}
	}
}
